﻿Imports System.Text

Public Class CommonTableToTrigger

    Private Shared Function 列(ByVal dr As DataRow) As String

        Dim sb As New StringBuilder
        sb.Append("DECLARE @" & CStr(dr("columnname")) & " " & CStr(dr("typename")))
        sb.Append("(")

        If CInt(dr("precision")) <> 0 Then
            sb.Append(CInt(dr("precision")) & "," & CInt(dr("scale")))
        ElseIf CInt(dr("max_length")) <> 0 Then
            If CInt(dr("max_length")) <> -1 Then
                If CStr(dr("typename")) = "nvarchar" Then
                    sb.Append(CInt(dr("max_length")) / 2)
                Else
                    sb.Append(CInt(dr("max_length")))
                End If
            Else
                sb.Append("MAX")
            End If
        End If
        sb.Append(")")

        Return sb.ToString

    End Function

    Private Shared Function 列1(ByVal dr As DataRow) As String

        Dim sb As New StringBuilder

        sb.Append("@" & CStr(dr("columnname")) & " = " & CStr(dr("columnname")))

        Return sb.ToString

    End Function

    Private Shared Function 列2(ByVal dr As DataRow) As String

        Dim sb As New StringBuilder

        sb.Append(CStr(dr("columnname")))

        Return sb.ToString

    End Function

    Private Shared Function 列3(ByVal dr As DataRow) As String

        Dim sb As New StringBuilder

        sb.Append("@" & CStr(dr("columnname")))

        Return sb.ToString

    End Function

    Private Shared Function Get999Collumn(ByVal dtConver As DataTable) As String

        For index As Integer = dtConver.Rows.Count - 1 To 0 Step -1
            If dtConver.Rows(index)("columnname").ToString.Contains("999") Then
                Return CStr(dtConver.Rows(index)("columnname"))
            End If
        Next

        Return ""

    End Function

    Public Shared Function ConvertToUpdateTrigger(ByVal dtConver As DataTable, ByVal tableName As String, ByVal prefix As String) As String

        Dim sb As New StringBuilder

        sb.AppendLine("ALTER TRIGGER [dbo].[" + prefix + "_U_" + tableName + "]")
        sb.AppendLine("ON [dbo].[" + tableName + "]")
        sb.AppendLine("AFTER UPDATE")
        sb.AppendLine("AS")

        Dim d999Cl As String = Get999Collumn(dtConver)
        If d999Cl.Trim <> "" Then
            sb.AppendLine("If UPDATE(" + d999Cl + ") ")
            sb.AppendLine(" Return")
        End If
        'If UPDATE(BIS07C999) Then
        ' Return
        '判断状态
        sb.AppendLine("")
        sb.AppendLine(" DECLARE @STATUS INT")
        sb.AppendLine(" SELECT @STATUS = [Status]  FROM " + prefix + "_Control WITH (NOLOCK) WHERE Kubun='001'")
        sb.AppendLine(" IF @STATUS <>1")
        sb.AppendLine("  RETURN")
        sb.AppendLine("")

        sb.AppendLine(" DECLARE @" + prefix + "_Key decimal(20,6)")
        sb.AppendLine(" DECLARE @" + prefix + "_Type decimal(1,0)")
        sb.AppendLine(" DECLARE @" + prefix + "_UpdateTimer decimal(20,6)")
        sb.AppendLine(" DECLARE @COUNT   decimal(10)")
        sb.AppendLine(" DECLARE @" + prefix + "_UpdateId int")

        For rowColumn As Integer = 0 To dtConver.Rows.Count - 1
            sb.AppendLine(" " & 列(dtConver.Rows(rowColumn)))
        Next

        sb.AppendLine("")

        sb.AppendLine(" SELECT")
        sb.AppendLine(" @COUNT=COUNT(" + CStr(dtConver.Rows(0)("columnname")) + ")")
        sb.AppendLine(" FROM deleted WITH (NOLOCK) ")
        sb.AppendLine(" IF @COUNT>0")
        sb.AppendLine(" BEGIN")

        sb.AppendLine("DECLARE deletedCursor CURSOR FOR")
        sb.AppendLine("						 SELECT ")
        For rowColumn As Integer = 0 To dtConver.Rows.Count - 1
            If dtConver.Rows.Count > 1 Then
                If rowColumn = 0 Then
                    sb.AppendLine(" " & CStr(dtConver.Rows(rowColumn)("columnname")))
                Else
                    sb.AppendLine("," & CStr(dtConver.Rows(rowColumn)("columnname")))
                End If
            Else
                sb.AppendLine(" " & CStr(dtConver.Rows(rowColumn)("columnname")))
            End If
        Next
        sb.AppendLine("FROM deleted WITH (NOLOCK) ")

        sb.AppendLine("")
        sb.AppendLine("         OPEN deletedCursor")
        sb.AppendLine("         FETCH NEXT FROM deletedCursor INTO ")
        For rowColumn As Integer = 0 To dtConver.Rows.Count - 1
            If dtConver.Rows.Count > 1 Then
                If rowColumn = 0 Then
                    sb.AppendLine("          @" & CStr(dtConver.Rows(rowColumn)("columnname")))
                Else
                    sb.AppendLine("         ,@" & CStr(dtConver.Rows(rowColumn)("columnname")))
                End If
            Else
                sb.AppendLine("          @" & CStr(dtConver.Rows(rowColumn)("columnname")))
            End If
        Next

        sb.AppendLine("         ")
        sb.AppendLine("         WHILE(@@FETCH_STATUS <> -1)")
        sb.AppendLine("         BEGIN")
        sb.AppendLine("         	IF @@FETCH_STATUS <> -2")
        sb.AppendLine("         	BEGIN")

        sb.AppendLine("")

        sb.AppendLine("         SELECT @" + prefix + "_UpdateId=ISNULL(max(ID),0) FROM " + prefix + "_Count WITH (NOLOCK) ")
        sb.AppendLine("         SELECT @" + prefix + "_UpdateId=@" + prefix + "_UpdateId+1")
        'sb.AppendLine("         UPDATE " + prefix + "_Control SET ID=@" + prefix + "_UpdateId WHERE Kubun='001'")

        sb.AppendLine("         SELECT  top 1 @" + prefix + "_Key= Key_Timer From " + prefix + "_Key order by Key_Timer desc")
        sb.AppendLine("         SELECT @" + prefix + "_UpdateTimer=CAST(REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(40),CURRENT_TIMESTAMP,121),'-',''),':',''),' ','') AS DECIMAL(20,6)) ")
        sb.AppendLine("         SELECT @" + prefix + "_Type= 2 ")
        sb.AppendLine("         ")

        sb.AppendLine("         ")

        sb.AppendLine("         INSERT INTO [" + prefix + "_" + tableName + "]")
        sb.AppendLine("                    (")
        sb.AppendLine("          " + prefix + "_Key")
        sb.AppendLine("         ," + prefix + "_Type")
        sb.AppendLine("         ," + prefix + "_UpdateTimer")
        sb.AppendLine("         ," + prefix + "_UpdateId")
        For rowColumn As Integer = 0 To dtConver.Rows.Count - 1
            sb.AppendLine("         ," & 列2(dtConver.Rows(rowColumn)))
        Next
        sb.AppendLine("                    )")

        sb.AppendLine("              VALUES")
        sb.AppendLine("                    (")
        sb.AppendLine("          @" + prefix + "_Key")
        sb.AppendLine("         ,@" + prefix + "_Type")
        sb.AppendLine("         ,@" + prefix + "_UpdateTimer")
        sb.AppendLine("         ,@" + prefix + "_UpdateId")
        For rowColumn As Integer = 0 To dtConver.Rows.Count - 1
            sb.AppendLine("         ," & 列3(dtConver.Rows(rowColumn)))
        Next
        sb.AppendLine("                    )")

        sb.AppendLine("         ")
        sb.AppendLine("         EXEC  " + prefix + "_P_Table @" + prefix + "_Key ,'" + prefix + "_" + tableName + "',2" & ",@" + prefix + "_UpdateId")

        sb.AppendLine("         		FETCH NEXT FROM deletedCursor INTO ")
        For rowColumn As Integer = 0 To dtConver.Rows.Count - 1
            If dtConver.Rows.Count > 1 Then
                If rowColumn = 0 Then
                    sb.AppendLine("         		 @" & CStr(dtConver.Rows(rowColumn)("columnname")))
                Else
                    sb.AppendLine("         		,@" & CStr(dtConver.Rows(rowColumn)("columnname")))
                End If
            Else
                sb.AppendLine("          @" & CStr(dtConver.Rows(rowColumn)("columnname")))
            End If
        Next
        sb.AppendLine("         		")

        sb.AppendLine("         	END")
        sb.AppendLine("         END")

        sb.AppendLine("         CLOSE deletedCursor")
        sb.AppendLine("         DEALLOCATE deletedCursor    ")

        sb.AppendLine(" END")

        sb.AppendLine(" SELECT")
        sb.AppendLine(" @COUNT=COUNT(" + CStr(dtConver.Rows(0)("columnname")) + ")")
        sb.AppendLine(" FROM inserted WITH (NOLOCK) ")
        sb.AppendLine(" IF @COUNT>0")
        sb.AppendLine(" BEGIN")

        sb.AppendLine("DECLARE insertedCursor CURSOR FOR")
        sb.AppendLine("						  SELECT ")
        For rowColumn As Integer = 0 To dtConver.Rows.Count - 1
            If dtConver.Rows.Count > 1 Then
                If rowColumn = 0 Then
                    sb.AppendLine(" " & CStr(dtConver.Rows(rowColumn)("columnname")))
                Else
                    sb.AppendLine("," & CStr(dtConver.Rows(rowColumn)("columnname")))
                End If
            Else
                sb.AppendLine(" " & CStr(dtConver.Rows(rowColumn)("columnname")))
            End If
        Next
        sb.AppendLine("FROM inserted  WITH (NOLOCK) ")

        sb.AppendLine("")
        sb.AppendLine("         OPEN insertedCursor")
        sb.AppendLine("         FETCH NEXT FROM insertedCursor INTO ")
        For rowColumn As Integer = 0 To dtConver.Rows.Count - 1
            If dtConver.Rows.Count > 1 Then
                If rowColumn = 0 Then
                    sb.AppendLine("          @" & CStr(dtConver.Rows(rowColumn)("columnname")))
                Else
                    sb.AppendLine("         ,@" & CStr(dtConver.Rows(rowColumn)("columnname")))
                End If
            Else
                sb.AppendLine("          @" & CStr(dtConver.Rows(rowColumn)("columnname")))
            End If
        Next

        sb.AppendLine("         ")
        sb.AppendLine("         WHILE(@@FETCH_STATUS <> -1)")
        sb.AppendLine("         BEGIN")
        sb.AppendLine("         	IF @@FETCH_STATUS <> -2")
        sb.AppendLine("         	BEGIN")

        sb.AppendLine("")

        sb.AppendLine("SELECT @" + prefix + "_UpdateId=ISNULL(max(ID),0) FROM " + prefix + "_Count WITH (NOLOCK) ")
        sb.AppendLine("SELECT @" + prefix + "_UpdateId=@" + prefix + "_UpdateId+1")
        'sb.AppendLine("UPDATE " + prefix + "_Control SET ID=@" + prefix + "_UpdateId WHERE Kubun='001'")

        sb.AppendLine("SELECT  top 1 @" + prefix + "_Key= Key_Timer From " + prefix + "_Key order by Key_Timer desc")
        sb.AppendLine("SELECT @" + prefix + "_UpdateTimer=CAST(REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(40),CURRENT_TIMESTAMP,121),'-',''),':',''),' ','') AS DECIMAL(20,6)) ")
        sb.AppendLine("SELECT @" + prefix + "_Type= 3")
        sb.AppendLine("")

        sb.AppendLine("")

        sb.AppendLine("INSERT INTO [" + prefix + "_" + tableName + "]")
        sb.AppendLine("           (")
        sb.AppendLine(" " + prefix + "_Key")
        sb.AppendLine("," + prefix + "_Type")
        sb.AppendLine("," + prefix + "_UpdateTimer")
        sb.AppendLine("," + prefix + "_UpdateId")
        For rowColumn As Integer = 0 To dtConver.Rows.Count - 1
            sb.AppendLine("," & 列2(dtConver.Rows(rowColumn)))
        Next
        sb.AppendLine("           )")

        sb.AppendLine("     VALUES")
        sb.AppendLine("           (")
        sb.AppendLine(" @" + prefix + "_Key")
        sb.AppendLine(",@" + prefix + "_Type")
        sb.AppendLine(",@" + prefix + "_UpdateTimer")
        sb.AppendLine(",@" + prefix + "_UpdateId")
        For rowColumn As Integer = 0 To dtConver.Rows.Count - 1
            sb.AppendLine("," & 列3(dtConver.Rows(rowColumn)))
        Next
        sb.AppendLine("           )")

        sb.AppendLine("")

        sb.AppendLine("EXEC  " + prefix + "_P_Table @" + prefix + "_Key ,'" + prefix + "_" + tableName + "',3" & ",@" + prefix + "_UpdateId")

        sb.AppendLine("         		FETCH NEXT FROM insertedCursor INTO ")
        For rowColumn As Integer = 0 To dtConver.Rows.Count - 1
            If dtConver.Rows.Count > 1 Then
                If rowColumn = 0 Then
                    sb.AppendLine("         		 @" & CStr(dtConver.Rows(rowColumn)("columnname")))
                Else
                    sb.AppendLine("         		,@" & CStr(dtConver.Rows(rowColumn)("columnname")))
                End If
            Else
                sb.AppendLine("          @" & CStr(dtConver.Rows(rowColumn)("columnname")))
            End If
        Next
        sb.AppendLine("         		")

        sb.AppendLine("         	END")
        sb.AppendLine("         END")

        sb.AppendLine("         CLOSE insertedCursor")
        sb.AppendLine("         DEALLOCATE insertedCursor    ")

        sb.AppendLine(" END")

        Return sb.ToString

    End Function

    Public Shared Function ConvertToInsertTriggerHAN07X002JIKKOU(ByVal prefix As String) As String

        Dim content As String = FileUtil.GetTriggerContent(System.IO.Path.Combine(System.Windows.Forms.Application.StartupPath, "xsc\HAN07X002JIKKOU_I.txt"))
        Return content.Replace("[prefix]", prefix)

    End Function

End Class


